--Example 1. Create a view of just history majors, showing names and Ids.

CREATE VIEW HISTMAJ (last, first,StudentId)
AS	SELECT	lastName, firstName,StuId
	FROM	Student
	WHERE	major = 'History';

--check the records in the view
SELECT * FROM HISTMAJ;

--Example 2.Create a view of class, but without facid.
CREATE VIEW 	ClassLoc
AS	SELECT	classNumber, schedule, room
	FROM	Class;


--check the records in the view
SELECT * FROM ClassLoc;

--Example 3.Create a view that shows a class list for CSC201A 
CREATE VIEW NewClassList
AS	SELECT	Student.stuId,lastName, firstName
	FROM	Enroll,Student
	WHERE	classNumber = 'CSC201A'
		AND Enroll.StuId = Student.StuId;
	
--check the records in the view
SELECT * FROM NewClassList;

--Example 4.Create a view that shows class numbers and rooms only.
CREATE VIEW ClassLoc2
AS	SELECT	classNumber, room
	FROM		ClassLoc;

--check the records in the view
SELECT * FROM ClassLoc2;

--Example 5.Create a view that shows class numbers and the number of students in each.
CREATE VIEW ClassCount2 (classNumber, TotCount)
AS	SELECT	classNumber, COUNT(*)
	FROM	Enroll
	GROUP BY  classNumber;

--check the records in the view
SELECT * FROM ClassCount2;

--Example 6.Using the ClassLoc view, find all classes that meet in rooms starting with H.
SELECT 	*
FROM 	ClassLoc
WHERE 	room LIKE 'H%';


--Inserting in a view

CREATE VIEW StudentVw2(stuId, lastName, firstName, credits)
AS	SELECT stuid, lastname, firstName, credits
	FROM Student;


INSERT
INTO StudentVw2
VALUES ('S1040', 'Levine', 'Adam', 30);

-- check that the record is in the Student table now

SELECT * FROM Student;